﻿Imports System.Data
Imports System.Data.SqlClient
Public Class Frm_Paiement_Personnels
    Public num = -1
    Public numf = -1
    Public modif = 0
    Public Sub vider()
        Comb_Personnels.Items.Clear()
        Comb_Personnels.Text = ""
        Comb_catégor.Items.Clear()
        Comb_catégor.Text = ""
        txt_a.Clear()
        txt_de.Text = ""
        txt_tache.Clear()
        txt_Remunération.Clear()
        txt_mont_paye.Clear()
        txt_remarq_paye.Clear()
        txt_rest.Clear()
        txt_total_paye.Clear()
        List_tous.Items.Clear()
    End Sub
    Public Sub reste()
        txt_rest.Clear()

        If txt_total_paye.Text <> "" And txt_Remunération.Text <> "" Then
            txt_rest.Text = CDbl(txt_Remunération.Text) - CDbl(txt_total_paye.Text)

        End If

    End Sub
    Public Sub total_paye()
        If Con.State = ConnectionState.Open Then
            cnClose()
        End If

        Try
            txt_total_paye.Clear()
            Cmd = New SqlCommand("select Montant  from Personnels_Paiement,Personnels Where Personnels.Id_Personnels=Personnels_Paiement.Id_Personnels and id_dmande=" & id_event & " and categorie ='" & Comb_catégor.SelectedItem.ToString & "' and De='" & txt_de.Text & "' and (Nom + '  ' + Prenom) ='" & Comb_Personnels.SelectedItem.ToString & "'", Con)
            open()
            Dr = Cmd.ExecuteReader()
            If Dr.HasRows = True Then
                Dr.Close()
                Cmd = New SqlCommand("select sum(Montant) as sume from Personnels_Paiement,Personnels Where Personnels.Id_Personnels=Personnels_Paiement.Id_Personnels and id_dmande=" & id_event & " and categorie ='" & Comb_catégor.SelectedItem.ToString & "' and De='" & txt_de.Text & "' and (Nom + '  ' + Prenom) ='" & Comb_Personnels.SelectedItem.ToString & "'", Con)
                Dr = Cmd.ExecuteReader
                Dr.Read()
                txt_total_paye.Text = Dr("sume")
            End If



            cnClose()

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
    End Sub
    Public Sub affi()
        Try
            Comb_Personnels.Items.Clear()
            Cmd = New SqlCommand("select distinct Nom,Prenom from Personnels, Personnels_Travail where Personnels.Id_Personnels=Personnels_Travail.Id_Personnels and id_dmande=" & id_event, Con)
            open()
            Dr = Cmd.ExecuteReader()
            Do While Dr.Read()
                Comb_Personnels.Items.Add(Dr("Nom") & "  " & Dr("Prenom"))
            Loop
            Dr.Close()
            cnClose()
            If Comb_Personnels.Items.Count > 0 Then
                Comb_Personnels.SelectedIndex = 0
            End If
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
    End Sub
    Public Sub affiche_paye()
        If Comb_Personnels.Items.Count > 0 Then
            If Con.State = ConnectionState.Open Then
                cnClose()
            End If

            Try
                Cmd = New SqlCommand("Select Id_Personnels,Nom,Prenom From Personnels", Con)
                open()
                Dr = Cmd.ExecuteReader()
                Do While Dr.Read()

                    If (Dr("Nom") & "  " & Dr("Prenom")) = Comb_Personnels.SelectedItem.ToString Then
                        num = Dr("Id_Personnels")
                    End If

                Loop
                Dr.Close()
                cnClose()
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                cnClose()
            End Try

            Try
                List_tous.Items.Clear()

                Cmd = New SqlCommand("Select * From Personnels_Paiement Where Id_Personnels =" & num & " and id_dmande=" & id_event & " and de='" & txt_de.Text & "'", Con)
                open()
                Dr = Cmd.ExecuteReader()
                Do While Dr.Read()
                    Dim Line As ListViewItem = New ListViewItem(New String() {Dr("Montant"), Dr("Date_m"), Dr("Remarque"), Dr("id_piem")})
                    List_tous.Items.Add(Line)
                Loop
                Dr.Close()
                cnClose()
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                cnClose()
            End Try
        End If
    End Sub
    Private Sub Frm_Paiement_Personnels_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub Comb_Personnels_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Comb_Personnels.SelectedIndexChanged
        If Con.State = ConnectionState.Open Then
            cnClose()
        End If
        Try
            Cmd = New SqlCommand("Select Id_Personnels,Nom,Prenom From Personnels", Con)
            open()
            Dr = Cmd.ExecuteReader()
            Do While Dr.Read()

                If (Dr("Nom") & "  " & Dr("Prenom")) = Comb_Personnels.SelectedItem.ToString Then
                    num = Dr("Id_Personnels")
                End If

            Loop
            Dr.Close()
            cnClose()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try

        Try
            Comb_catégor.Items.Clear()
            Cmd = New SqlCommand("Select Categorie From Personnels Where (Nom + '  ' + Prenom) ='" & Comb_Personnels.SelectedItem.ToString & "'", Con)
            open()
            Dr = Cmd.ExecuteReader()
            If Dr.HasRows Then
                Do While Dr.Read
                    Comb_catégor.Items.Add(Dr("Categorie"))
                Loop
            End If

            Dr.Close()
            cnClose()

            If Comb_catégor.Items.Count > 0 Then
                Comb_catégor.SelectedIndex = 0
            End If
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
        '__________________________________________________________
        Try

            Cmd = New SqlCommand("Select A,Remuneration,Taches,De From Personnels,Personnels_Travail Where Personnels.Id_Personnels=Personnels_Travail.Id_Personnels and Personnels_Travail.Id_Personnels =" & num & " and id_dmande=" & id_event & " and (Nom + '  ' + Prenom) ='" & Comb_Personnels.SelectedItem.ToString & "'", Con)
            open()
            Dr = Cmd.ExecuteReader()
            If Dr.HasRows Then
                Do While Dr.Read
                    txt_de.Text = Dr("De")
                    txt_a.Text = Dr("A")
                    txt_Remunération.Text = Dr("Remuneration")
                    txt_tache.Text = Dr("Taches")
                Loop
            End If

            Dr.Close()
            cnClose()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
        '_____________________________________________________________________'

        affiche_paye()
        total_paye()
        reste()
    End Sub

    Private Sub Comb_de_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
        'If Con.State = ConnectionState.Open Then
        '    cnClose
        'End If
        'Try
        '    Cmd = New SqlCommand("Select Id_Personnels,Nom,Prenom From Personnels", Con)
        '    open()
        '    Dr = Cmd.ExecuteReader()
        '    Do While Dr.Read()

        '        If (Dr("Nom") & "  " & Dr("Prenom")) = Comb_Personnels.SelectedItem.ToString Then
        '            num = Dr("Id_Personnels")
        '        End If

        '    Loop
        '    Dr.Close()
        '    cnClose
        'Catch ex As Exception
        '    MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        '    cnClose
        'End Try
        'Try
        '    Comb_a.Items.Clear()
        '    txt_Remunération.Clear()
        '    txt_tache.Clear()

        '    Cmd = New SqlCommand("Select A,Remuneration,Taches From Personnels,Personnels_Travail Where Personnels.Id_Personnels=Personnels_Travail.Id_Personnels and Personnels_Travail.Id_Personnels =" & num & " and id_dmande=" & id_event & " and categorie ='" & Comb_catégor.SelectedItem.ToString & "' and De='" & Comb_de.SelectedItem.ToString & "' and (Nom + '  ' + Prenom) ='" & Comb_Personnels.SelectedItem.ToString & "'", Con)
        '    open()
        '    Dr = Cmd.ExecuteReader()
        '    If Dr.HasRows Then
        '        Dr.Read()
        '        Comb_a.Text = Dr("A")
        '        txt_Remunération.Text = Dr("Remuneration")
        '        txt_tache.Text = Dr("Taches")
        '    End If

        '    Dr.Close()
        '    cnClose
        'Catch ex As Exception
        '    MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        '    cnClose
        'End Try

        'total_paye()
        'affiche_paye()
        'reste()
    End Sub

    Private Sub btn_ajouter_paye_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_ajouter_paye.Click


        If txt_mont_paye.Text <> "" And txt_client.Text <> "" And txt_date_evenement.Text <> "" Then
            Try
                Cmd = New SqlCommand("Select Id_Personnels,Nom,Prenom From Personnels", Con)
                open()
                Dr = Cmd.ExecuteReader()
                Do While Dr.Read()

                    If (Dr("Nom") & "  " & Dr("Prenom")) = Comb_Personnels.SelectedItem.ToString Then
                        num = Dr("Id_Personnels")
                    End If

                Loop
                Dr.Close()
                cnClose()
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                cnClose()
            End Try
            '____________________________________________________________________________________'

            If MsgBox("Vous Voulez Vraiment Ajouter ?", MsgBoxStyle.YesNo Or MsgBoxStyle.Information, "Ajouter") = MsgBoxResult.Yes Then
                Try
                    Cmd = New SqlCommand("insert into Personnels_Paiement (Id_Personnels,id_dmande,Montant,Date_m,Remarque,de)" & _
                    "values (@Id_Personnels,@id_demande,@Montant,@Date_m,@Remarque,@de)", Con)

                    Cmd.Parameters.Add("@Id_Personnels", SqlDbType.Int).Value = num
                    Cmd.Parameters.Add("@id_demande", SqlDbType.Int).Value = id_event
                    Cmd.Parameters.Add("@Montant", SqlDbType.Float).Value = txt_mont_paye.Text
                    Cmd.Parameters.Add("@Date_m", SqlDbType.Date).Value = Date_paye.Value.Date
                    Cmd.Parameters.Add("@Remarque", SqlDbType.Text).Value = txt_remarq_paye.Text
                    Cmd.Parameters.Add("@de", SqlDbType.VarChar).Value = txt_de.Text

                    open()
                    Cmd.ExecuteNonQuery()
                    cnClose()
                    MsgBox("Montant Ajouté", MsgBoxStyle.Information, "Réussi !")
                    txt_mont_paye.Clear()
                    txt_remarq_paye.Clear()
                    affiche_paye()
                    total_paye()
                    reste()

                Catch ex As Exception
                    MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                    cnClose()
                End Try
            End If
        End If
    End Sub

    Private Sub btn_supp_paye_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_supp_paye.Click
        If MsgBox("Voulez Vous Vraiment Supprimer", MsgBoxStyle.YesNo Or MsgBoxStyle.Information, "Supprission !") = MsgBoxResult.Yes Then
            Dim n As Integer
            With List_tous
                If .SelectedIndices.Count > 0 Then
                    For i As Integer = 0 To .SelectedIndices.Count - 1
                        n = CInt(.Items(.SelectedIndices(i)).SubItems(3).Text)
                    Next
                    Try
                        Cmd = New SqlCommand("Delete From Personnels_Paiement Where id_piem =" & n, Con)
                        open()
                        Cmd.ExecuteNonQuery()
                        cnClose()
                        modif = 0
                        MsgBox("Montant Supprimer ", MsgBoxStyle.Information, "Réussi !")
                        txt_mont_paye.Clear()
                        txt_remarq_paye.Clear()
                        affiche_paye()
                        total_paye()
                        reste()

                    Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
                        cnClose()
                    End Try

                Else
                    MsgBox("Vous n'avez selectionner aucun ligne !", MsgBoxStyle.Critical, "Réssayer !")
                End If
            End With
        End If
    End Sub

    Private Sub List_tous_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles List_tous.SelectedIndexChanged
        Dim n As Integer
        With List_tous
            If .SelectedIndices.Count > 0 Then
                For i As Integer = 0 To .SelectedIndices.Count - 1
                    n = CInt(.Items(.SelectedIndices(i)).SubItems(3).Text)
                    modif = CInt(.Items(.SelectedIndices(i)).SubItems(3).Text)
                Next
            End If
        End With
        txt_mont_paye.Clear()
        txt_remarq_paye.Clear()

        Try
            Cmd = New SqlCommand("Select * From Personnels_Paiement where id_piem =" & n, Con)
            open()
            Dr = Cmd.ExecuteReader()
            Dr.Read()
            If Dr.HasRows Then
                txt_mont_paye.Text = Dr("Montant")
                Date_paye.Text = Dr("Date_m")
                txt_remarq_paye.Text = Dr("Remarque")
            End If

            Dr.Close()
            cnClose()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation)
            cnClose()
        End Try
    End Sub

    Private Sub btn_modif_paye_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        'If modif <> 0 Then
        '    If txt_mont_paye.Text <> "" Then
        '        If MsgBox("Vous Voulez Vraiment Modifier ?", MsgBoxStyle.YesNo Or MsgBoxStyle.Information, "Modifier !") = MsgBoxResult.Yes Then
        '            Try

        '                Cmd = New SqlCommand("update Personnels_Paiement set Montant=@Montant,Date_m=@Date_m,Remarque=@Remarque" & _
        '                " Where id_piem=" & modif, Con)
        '                Cmd.Parameters.Add("@Montant", SqlDbType.Float).Value = txt_mont_paye.Text
        '                Cmd.Parameters.Add("@Date_m", SqlDbType.Date).Value = Date_paye.Value.Date
        '                Cmd.Parameters.Add("@Remarque", SqlDbType.Text).Value = txt_remarq_paye.Text


        '                open()
        '                Cmd.ExecuteNonQuery()
        '                cnClose

        '                modif = 0
        '                MsgBox("Montant Modifier ", MsgBoxStyle.Information, "Réussi !")
        '                txt_mont_paye.Clear()
        '                txt_remarq_paye.Clear()
        '                affiche_paye()
        '                total_paye()
        '                reste()
        '            Catch ex As Exception
        '                MsgBox(ex.Message, MsgBoxStyle.Exclamation)
        '                cnClose
        '            End Try
        '        End If
        '    End If
        'End If
    End Sub

    Private Sub ButtonX1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX1.Click
        Frm_liste_Demande.MdiParent = Frm_Main
        Frm_liste_Demande.Show()
        liste = "Pers_pai"
    End Sub

  
End Class